﻿<?php
define('AJAX_SCRIPT', true);
require_once('../../../config.php');
require_once ($CFG->dirroot .'/local/myplugin/PHPExcel.php');
global $DB;

//lấy mã chương trình đào tạo
$program = $_GET['program']; //required_param('program', PARAM_TEXT);
$majorlist = $_GET['majors']; //optional_param('majors', null, PARAM_TEXT);
$subjectlist = $_GET['subjects']; //optional_param('subjects', null, PARAM_TEXT);

if($program == "not") {
	echo '<div>'.constant("PROGRAM_NOT_SELECT").'</div>';
}
else {
	//Lấy danh sách các ngành thuộc chương trình
	$n_majors = 0;
	if (strcmp($majorlist, "")>0)
	{
		$majors=explode('*', $majorlist);	//tách mã chuyên ngành
		$n_majors = count($majors);
	}

	// Create new PHPExcel object
	$objPHPExcel = new PHPExcel();
	//Định dạng
	$border = array('borders' => array('allborders' => 
				array('style' => PHPExcel_Style_Border::BORDER_THIN)));


	//Write data to objPHPExcel
	//Phần mở đầu
	$objPHPExcel->setActiveSheetIndex(0)
				->setCellValue('A1', 'ĐẠI HỌC QUỐC GIA TP.HCM')
				->setCellValue('A2', 'TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN')
				->setCellValue('D1', 'CỘNG HÒA XÃ HỘI CHỦ NGHĨA VIỆT NAM')
				->setCellValue('D2', 'Độc lập - Tự do - Hạnh phúc')
				->setCellValue('D3', '-------------')
				->setCellValue('A5', 'CHƯƠNG TRÌNH GIÁO DỤC ĐẠI HỌC')
				->setCellValue('A6', 'ĐỀ XUẤT CHỈNH SỬA DỰA THEO ĐỀ ÁN CDIO')
				->setCellValue('A7', 'NHÓM NGÀNH CÔNG NGHỆ THÔNG TIN')
				->setCellValue('A8', 'Tên chương trình		:')	
				->setCellValue('A9', 'Trình độ đào tạo		:')
				->setCellValue('A10', 'Loại hình đào tạo	:')
				->setCellValue('A12', 'CẤU TRÚC CHƯƠNG TRÌNH');
	$objPHPExcel->getActiveSheet()->getStyle('A12')->getFont()->setBold(true);	
	$row = 13;
	//===============================Thông tin chung về chương trình đào tạo========================
	//Lấy thông tin về chương trình đào tạo từ CSDL
	$programinfo = $DB->get_record("program",array('id'=>$program));
	//Trình bày thông tin chương trình đào tạo lên file
	$objPHPExcel->setActiveSheetIndex(0)
				->setCellValue('C8', $programinfo->program_name)
				->setCellValue('C9', $programinfo->program_type)
				->setCellValue('C10', $programinfo->program_level);

	//===============Thông tin về các khối kiến thức trong chương trình đào tạo==========================
	//Lấy thông tin các khối kiến thức trong chương trình
	$blocks = $DB->get_records('block_of_knowledge', array('program_id'=>$program));

	//Trình bày thông tin các khối kiến thức 		
	foreach($blocks as $blockinfo) {
		$row++;
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$row, $blockinfo->bok_name);
		$objPHPExcel->getActiveSheet()->getStyle('A'.$row)->getFont()->setBold(true);
		$objPHPExcel->getActiveSheet()->getStyle('A'.$row)->getFont()->setSize(12);			
		$row++;
		//Lấy danh sách môn học trong từng block
		$sql = "SELECT mdl_subject.subject_id, subject_name_vn, subject_credit, subject_theory_hr, subject_practice_hr, abstract ".
				"FROM mdl_subject inner join  mdl_subject_block on mdl_subject_block.subject_id = mdl_subject.id". 
				" WHERE mdl_subject_block.blockofknowledge_id = ". $blockinfo->id;
		$subjects = $DB->get_records_sql($sql);
		//Trình bày thông tin môn học lên file
		$objPHPExcel->setActiveSheetIndex(0)
					->setCellValue('A'.$row, 'STT')
					->setCellValue('B'.$row, 'MÃ SỐ')
					->setCellValue('C'.$row, 'TÊN HỌC PHẦN')
					->setCellValue('D'.$row, 'TC')
					->setCellValue('E'.$row, 'LT')
					->setCellValue('F'.$row, 'TH');
					
		$objPHPExcel->getActiveSheet()->getStyle('A'.$row.":".'F'.$row)->getFont()->setBold(true);
		$objPHPExcel->getActiveSheet()->getStyle('A'.$row.":".'F'.$row)->getFont()->setSize(12);	
		$objPHPExcel->getActiveSheet()->getStyle('A'.$row.":".'F'.$row)->applyFromArray($border);
		$objPHPExcel->getActiveSheet()->getStyle('A'.$row.':F'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$row++;			
		$n = 1;
		foreach($subjects as $s) {
			$objPHPExcel->setActiveSheetIndex(0)
						->setCellValue('A'.$row, $n)
						->setCellValue('B'.$row, $s->subject_id)
						->setCellValue('C'.$row, $s->subject_name_vn)
						->setCellValue('D'.$row, $s->subject_credit)
						->setCellValue('E'.$row, $s->subject_theory_hr)
						->setCellValue('F'.$row, $s->subject_practice_hr);
						
			$objPHPExcel->getActiveSheet()->getStyle('A'.$row.":".'F'.$row)->applyFromArray($border);
			$objPHPExcel->getActiveSheet()->getStyle('D'.$row.':F'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$row++;
			$n++;
		}
	}

	//====================Thông tin về các chuyên ngành trong chương trình đào tạo=========================

	//Lấy thông tin từng chuyên ngành từ CSDL
	for($i=0; $i<$n_majors; $i++) {
		$major = $DB->get_record('major', array('id'=>$majors[$i]));
		$row++;
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$row, $major->major_name);
		$objPHPExcel->getActiveSheet()->getStyle('A'.$row)->getFont()->setBold(true);
		$objPHPExcel->getActiveSheet()->getStyle('A'.$row)->getFont()->setSize(12);	
		$row++;
		
		//Lấy các block của chuyên ngành đào tạo p
		$sql = "SELECT mdl_block_of_knowledge.id, mdl_block_of_knowledge.bok_name  ".
				" FROM mdl_block_of_knowledge inner join mdl_major_block on mdl_block_of_knowledge.id = mdl_major_block.blockofknowledge_id".
				" WHERE mdl_major_block.major_id = ".$majors[$i];
		$blocks = $DB->get_records_sql($sql);
		foreach($blocks as $b) {
			$objPHPExcel->setActiveSheetIndex(0)
						->setCellValue('A'.$row, $b->bok_name);
			$objPHPExcel->getActiveSheet()->getStyle('A'.$row)->getFont()->setBold(true);
			$row++;
			
			//Lấy danh sách môn học trong từng block
			$sql = "SELECT mdl_subject.subject_id, subject_name_vn, subject_credit, subject_theory_hr, subject_practice_hr, abstract ".
					"FROM mdl_subject inner join  mdl_subject_block on mdl_subject_block.subject_id = mdl_subject.id". 
					" WHERE mdl_subject_block.blockofknowledge_id = ". $b->id;
			$subjects = $DB->get_records_sql($sql);
			//Trình bày thông tin môn học lên file
			$objPHPExcel->setActiveSheetIndex(0)
						->setCellValue('A'.$row, 'STT')
						->setCellValue('B'.$row, 'MÃ SỐ')
						->setCellValue('C'.$row, 'TÊN HỌC PHẦN')
						->setCellValue('D'.$row, 'TC')
						->setCellValue('E'.$row, 'LT')
						->setCellValue('F'.$row, 'TH');
						
			$objPHPExcel->getActiveSheet()->getStyle('A'.$row.":".'F'.$row)->getFont()->setBold(true);
			$objPHPExcel->getActiveSheet()->getStyle('A'.$row.":".'F'.$row)->getFont()->setSize(12);	
			$objPHPExcel->getActiveSheet()->getStyle('A'.$row.":".'F'.$row)->applyFromArray($border);
			$objPHPExcel->getActiveSheet()->getStyle('A'.$row.':F'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$row++;	
			$n = 1;	
			foreach($subjects as $s) {
				$objPHPExcel->setActiveSheetIndex(0)
							->setCellValue('A'.$row, $n)
							->setCellValue('B'.$row, $s->subject_id)
							->setCellValue('C'.$row, $s->subject_name_vn)
							->setCellValue('D'.$row, $s->subject_credit)
							->setCellValue('E'.$row, $s->subject_theory_hr)
							->setCellValue('F'.$row, $s->subject_practice_hr);
							
				$objPHPExcel->getActiveSheet()->getStyle('A'.$row.":".'F'.$row)->applyFromArray($border);
				$objPHPExcel->getActiveSheet()->getStyle('D'.$row.':F'.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				$row++;
				$n++;
			}
		}
	}
	//=========================Mô tả môn học ===============================	
	//Lấy danh sách các môn học được chọn để xuất thông tin mô tả
	$n_subjects = 0;
	if (strcmp($subjectlist, "")>0)
	{	
		$subjects=explode('*', $subjectlist);	//tách mã môn học
		$n_subjects = count($subjects);
	}

	if($n_subjects>0) {
		$row++;
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$row, 'Giới thiệu môn học');
		$objPHPExcel->getActiveSheet()->getStyle('A'.$row)->getFont()->setBold(true);
		$objPHPExcel->getActiveSheet()->getStyle('A'.$row)->getFont()->setSize(12);			
		for($i=0; $i<$n_subjects; $i++) {
			$row++;
			$s = $DB->get_record('subject', array('id'=>$subjects[$i]));
			
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$row, $s->subject_name_vn);
			$objPHPExcel->getActiveSheet()->getStyle('A'.$row)->getFont()->setBold(true);
			$objPHPExcel->getActiveSheet()->getStyle('A'.$row)->getFont()->setSize(12);
			$row++;
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$row, $s->abstract);
			
			
		}
	}
		 
	//Set font
	$objPHPExcel->getActiveSheet()->getStyle('A1:Z1000')->getFont()->setName('Times New Roman');
	$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
	$objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(13);
	$objPHPExcel->getActiveSheet()->getStyle('D1:D2')->getFont()->setSize(14);
	$objPHPExcel->getActiveSheet()->getStyle('A5:I6')->getFont()->setSize(16);
	$objPHPExcel->getActiveSheet()->getStyle('A5:I6')->getFont()->setBold(true);
	$objPHPExcel->getActiveSheet()->getStyle('A7:JI7')->getFont()->setSize(14);

	//Merge cells
	$objPHPExcel->getActiveSheet()->mergeCells('A1:C1');
	$objPHPExcel->getActiveSheet()->mergeCells('A2:C2');			
	$objPHPExcel->getActiveSheet()->mergeCells('D1:I1');			
	$objPHPExcel->getActiveSheet()->mergeCells('D2:I2');
	$objPHPExcel->getActiveSheet()->mergeCells('D3:I3');
	$objPHPExcel->getActiveSheet()->mergeCells('A5:I5');			
	$objPHPExcel->getActiveSheet()->mergeCells('A6:I6');
	$objPHPExcel->getActiveSheet()->mergeCells('A7:I7');
	$objPHPExcel->getActiveSheet()->mergeCells('A8:B8');
	// Set column widths
	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10);
	$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(40);
	$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);
	$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);
	$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
	$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);


	//Alignment
	$objPHPExcel->getActiveSheet()->getStyle('D1:I1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle('D2:I2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle('D3:I3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle('A5:I5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle('A6:I6')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle('A7:I7')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	// Rename sheet
	$objPHPExcel->getActiveSheet()->setTitle('Chương trình giáo dục đại học');

	// Set active sheet index to the first sheet, so Excel opens this as the first sheet
	$objPHPExcel->setActiveSheetIndex(0);

	// Save Excel 2007 file

	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	//$objWriter->save(str_replace('.php', '.xlsx', "Subject.xlsx"));

	$objWriter->save($CFG->dirroot.'/local/myplugin/export/CURRICULUM_CDIO'.$program.'.xlsx');
	// Echo done
	echo '<a href="../export/CURRICULUM_CDIO'.$program.'.xlsx" onclick="hide_save_panel()"> Save File</a>';
}
